package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Optional;
import java.util.Set;


import com.google.gson.Gson;
import com.google.gson.GsonBuilder;


public class Item extends BasicItem {
	
	
	private static final String QUERY =
			"select  items.description, items.price,items.promotional_price,"+
					"brands.description as brand_description, figures.path as figure_path,"+ 
					"figures.color_id, colors.name as color_name, colors.path as color_path,"+   
					"colors.code as color_code, inventory.size_id, inventory.inventory_id as inventory_id, sizes.name as size_name, inventory.count,"+
					"im.PERCENTAGE, im.MATERIAL_ID as material_id, materials.DESCRIPTION as material_description, features.DESCRIPTION as feature_description,"+
					"ip.present_id as present_id, presents.DESCRIPTION as present_description, presents.FIGURE_PATH as present_figure_path,"+
					"ADDITIONAL_SIZES.ADDITIONAL_SIZE_DESCRIPTION as supplementary_size, "+
					"producers.description as producer, producers.id as producer_id "+
					"from items "+
					"left outer join producers on (items.producer_id = producers.id) "+
					"join item_materials im on (  items.id = im.item_id) "+
					"join materials on(im.material_id = materials.id) "+
					"left outer join item_presents ip on (items.id = ip.item_id) "+
					"left outer join presents on (ip.present_id = presents.id and presents.count>0) "+
					"left outer join features on(items.id = features.item_id) "+
					"left outer join brands on (items.brand_id = brands.id) "+
					"left outer join figures on (items.id = figures.item_id) "+
					"join colors on (figures.color_id = colors.id) "+
					"join clothing_inventory inventory on (items.id = inventory.item_id and figures.color_id =inventory.COLOR_ID ) "+
					"join sizes on (inventory.size_id = sizes.id) "+
					"left outer join ADDITIONAL_SIZES on (items.id = ADDITIONAL_SIZES.item_id and sizes.id = additional_sizes.size_id) "+
					"where items.id = ? and inventory.count>=?"+
					"order by colors.id, sizes.id "  ;
	private Set<Color> colors;
	private Set<Size> sizes;
	private Map<Integer, Color> idToColor;
	private Map<Integer, Size> idToSize;
	private Map<Integer, HashSet<Integer>> colorIdToSizeIds;
	private Map<Integer,HashSet<Integer>> sizeIdToColorIds;
	private Map<Integer,HashSet<Figure>> colorIdToFigures;
	private Map<Integer,HashSet<String>> sizeIdToDescriptions;
	private Set<Present> presents;
	private Set<String> features;
	private Set<Material> materials;
	private  Map<Integer, Integer> inventoryIdToCount;

	private Map<Integer, HashMap<Integer, Integer>> colorIdToSizeIdsToInventoryId;
	
	
	private Item(int id, String description, double price,
			double promotionalPrice, String brandDescription, Producer producer) {
		super(id, description, price, promotionalPrice, brandDescription, producer);
		colors = new HashSet<Color>();
		sizes = new HashSet<Size>();
		idToColor = new HashMap<Integer, Color>();
		idToSize = new HashMap<Integer,Size>();
		
		colorIdToSizeIds = new HashMap<Integer, HashSet<Integer>>();
		sizeIdToColorIds = new HashMap<Integer, HashSet<Integer>>();
		colorIdToFigures = new HashMap<Integer, HashSet<Figure>>();
		sizeIdToDescriptions = new HashMap<Integer,HashSet<String>>();
		presents = new HashSet<Present>();
		features = new HashSet<String>();
		materials = new HashSet<Material>();
		colorIdToSizeIdsToInventoryId = new HashMap<Integer, HashMap<Integer,Integer>>();
		inventoryIdToCount = new HashMap<Integer,Integer>();
	}
	
	/**
	 * @return the sizes
	 */
	public Set<Size> getSizes() {
		return sizes;
	}

	/**
	 * @return the idToColor
	 */
	public Map<Integer, Color> getIdToColor() {
		return idToColor;
	}

	/**
	 * @return the idToSize
	 */
	public Map<Integer, Size> getIdToSize() {
		return idToSize;
	}

	/**
	 * @return the colorIdToSizeIdsToInventoryId
	 */
	public Map<Integer, HashMap<Integer, Integer>> getColorIdToSizeIdsToInventoryId() {
		return colorIdToSizeIdsToInventoryId;
	}
	
	/**
	 * @return the colors
	 */
	public Set<Color> getColors() {
		return colors;
	}

	private void addColor(Color color){
		colors.add(color);
		idToColor.put(color.getId(), color);
		
	}
	private void addSize(Size size){
		sizes.add(size);
		idToSize.put(size.getId(), size);
	}
	
	private void addColorSizePair(Color color, Size size, int inventoryId){
		int colorId = color.getId();
		int sizeId = size.getId();
		HashSet<Integer> colors = sizeIdToColorIds.getOrDefault(sizeId, new HashSet<Integer>());
		HashSet<Integer> sizes = colorIdToSizeIds.getOrDefault(colorId, new HashSet<Integer>());
		colors.add(colorId);
		sizes.add(sizeId);
		colorIdToSizeIds.put(colorId, sizes);
		sizeIdToColorIds.put(sizeId, colors);
		HashMap<Integer,Integer> sizeToInventoryId = colorIdToSizeIdsToInventoryId.getOrDefault(colorId,new HashMap<Integer,Integer>()) ;
		sizeToInventoryId.put(sizeId, inventoryId);
		colorIdToSizeIdsToInventoryId.put(colorId, sizeToInventoryId);
	}
	
	private void addColorFigurePair(Color color, Figure figure){
		int colorId = color.getId();
		HashSet<Figure> figures = colorIdToFigures.getOrDefault(colorId, new HashSet<Figure>());
		figures.add(figure);
		colorIdToFigures.put(colorId, figures);
	}
	
	private void addDescriptionToSize(String description,Size size){
		int sizeId = size.getId();
		HashSet<String> descriptions = sizeIdToDescriptions.getOrDefault(sizeId, new HashSet<String>());
		descriptions.add(description);
		sizeIdToDescriptions.put(sizeId, descriptions);
	}
	
	private void addPresent(Present present){
		this.presents.add(present);
	}
	

	private void addFeature(String feature){
		this.features.add(feature);
	}

	
	private void addMaterial(Material material){
		this.materials.add(material);
	}
	
	private void addCount(int inventoryId, int count){
		inventoryIdToCount.put(inventoryId, count);
	}
	
	public static Optional<Item> getItem(int id, int minCount) throws DatabaseException{
			
			ResultSet rs=null;
			Item item = null;
			try(Connection con =ConnectionPool.getConnection(); 
					PreparedStatement statement = con.prepareStatement(QUERY);){
				statement.setInt(1, id); 
				statement.setInt(2, minCount); 
				rs = statement.executeQuery();
				Producer producer;
				Color color = null;
				Size size = null;
				Figure figure = null;
				Material material = null;
				Present present =null;
				
				String feature =null;
				String sizeDescription = null;
				int presentId = 0 ;
				int inventoryId = 0;
				int count = 0;
				while(rs.next()){
					if (item == null){ 
						producer = new Producer(rs.getInt("producer_id"),rs.getString("producer"));
						item = new Item(id, rs.getString("description"), rs.getDouble("price"), rs.getDouble("promotional_price"), rs.getString("brand_description"), producer);
					}
					color = new Color(rs.getInt("color_id"),rs.getString("color_name"),rs.getString("color_path"),rs.getString("color_code"));
					figure = new Figure(rs.getString("figure_path"),rs.getInt("color_id"));
					size = new Size(rs.getInt("size_id"),rs.getString("size_name"));
					sizeDescription = rs.getString("supplementary_size");
					feature = rs.getString("feature_description");
					inventoryId = rs.getInt("inventory_id");
					count = rs.getInt("count");
					material = new Material(rs.getInt("material_id"), rs.getString("material_description"), rs.getInt("percentage"));
					presentId = rs.getInt("present_id");
					if(presentId!=0){
						present = new Present(rs.getInt("present_id"),rs.getString("present_description"),rs.getString("present_figure_path"));
						item.addPresent(present);
					}
					item.addColor(color);
					item.addSize(size);
					item.addColorSizePair(color, size,inventoryId);
					item.addColorFigurePair(color, figure);
					item.addDescriptionToSize(sizeDescription, size);
					item.addMaterial(material);
					item.addFeature(feature);
					item.addCount(inventoryId, count);
					
				}
				
			} catch (SQLException e) {
				throw new DatabaseException(e);
			}finally{
				try {
					if(rs!=null){
						rs.close();
					}
				}catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
			return Optional.ofNullable(item);
		}


	/**
	 * @return the inventoryIdToCount
	 */
	public Map<Integer, Integer> getInventoryIdToCount() {
		return inventoryIdToCount;
	}

	public static String getItemJSON(Item item){
		Gson gson = new GsonBuilder().serializeNulls().create();
		return gson.toJson(item);
	}

	public static void main(String[] args) throws DatabaseException {
			Item item = getItem(2,0).get();
//			System.out.println(item);
			String json = getItemJSON(item);
			System.out.println(json);

	}

}


